package javacvtest.sqladminserver.service;


import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import javacvtest.sqladminserver.DTO.SqlSaveDTO;
import javacvtest.sqladminserver.Mapper.SqlSave;
import javacvtest.sqladminserver.result.Result;
import javacvtest.sqladminserver.DTO.SqlDatabaseDTO;
import javacvtest.sqladminserver.DTO.SqlUrlDTO;
import javacvtest.sqladminserver.VO.SqlResultVO;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.stereotype.Service;

import javax.sql.DataSource;
import java.math.BigDecimal;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

@Slf4j
@Service
public class SqlService {

    @Autowired
    private SqlSave sqlSave;

//    private DataSource dataSource; // 新增成员变量用于保存数据库连接

    public Result<List<String>> checkSql(SqlUrlDTO sqlUrlDTO) {

        try {
           DataSource dataSource = new DriverManagerDataSource(sqlUrlDTO.getDbDataBase(),
                    sqlUrlDTO.getDbUsername(), sqlUrlDTO.getDbPassword());
            log.info("数据库Url："+sqlUrlDTO.getDbDataBase());
            Connection connection = dataSource.getConnection();
            if (connection != null) {
                log.info("连接成功");

                List<String> dataList= new ArrayList<>();;
                try (var statement = connection.createStatement()) {
                    try (var dataResult = statement.executeQuery("show databases")) {
                        while (dataResult.next()) {
                            dataList.add(dataResult.getString(1));
                        }
                    }
                }
                connection.close();
                return Result.success(dataList);
            }
        } catch (SQLException e) {
            log.info("连接发生异常", e);
        }
        log.info("连接错误");
        return Result.error("数据库连接失败");
    }

    public SqlResultVO getSqlReslut(SqlDatabaseDTO sqlDatabaseDTO) {
        SqlResultVO result = new SqlResultVO();
        DataSource dataSource = new DriverManagerDataSource(sqlDatabaseDTO.getDbDatabase(),
                sqlDatabaseDTO.getDbUserName(), sqlDatabaseDTO.getDbPassword());

        log.info("数据库Url："+sqlDatabaseDTO.getDbDatabase());
        try (Connection connection = dataSource.getConnection()) {
            if (connection == null) {
                result.setMessage("无法获取数据库连接");
                return result;
            }

            try (var preparedStatement = connection.prepareStatement(sqlDatabaseDTO.getSql())) {
                try (var resultSet = preparedStatement.executeQuery()) {
                    log.info("执行SQL: " + sqlDatabaseDTO.getSql());

                    ResultSetMetaData resultFrame = resultSet.getMetaData();
                    log.info("将结果转换为元数据：" + resultFrame);
                    int columnCount = resultFrame.getColumnCount();

                    // 提取字段名
                    List<String> columns = new ArrayList<>();
                    for (int i = 1; i <= columnCount; i++) {
                        columns.add(resultFrame.getColumnName(i));
                    }

                    List<List<Object>> rowsData = new ArrayList<>();
                    while (resultSet.next()) {
                        List<Object> rowData = new ArrayList<>();
                        for (int i = 1; i <= columnCount; i++) {
                            int columnType = resultFrame.getColumnType(i);
                            Object value = null;
                            //格式化结果内容格式
                            try {
                                switch (columnType) {
                                    case Types.INTEGER:
                                    case Types.BIGINT:
                                        value = resultSet.getObject(i);
                                        break;
                                    case Types.DECIMAL:
                                    case Types.NUMERIC:
                                        BigDecimal decimal = resultSet.getBigDecimal(i);
                                        value = decimal.stripTrailingZeros();
                                        break;
                                    case Types.TIMESTAMP:
                                        Timestamp ts = resultSet.getTimestamp(i);
                                        value = ts != null ? new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(ts) : null;
                                        break;
                                    default:
                                        value = resultSet.getObject(i);
                                }
                            } catch (SQLException e) {
                                log.error("Error getting column {} value: {}", i, e.getMessage());
                                value = null;
                            }

                            rowData.add(value);
                        }
                        rowsData.add(rowData);
                    }

                    result.setColumns(columns);
                    result.setRows(rowsData);
                    result.setMessage("查询成功");
                    SqlSaveDTO sqlSaveDTO =new SqlSaveDTO();
                    sqlSaveDTO.setDataUser(sqlDatabaseDTO.getDataUser());
                    sqlSaveDTO.setUserName(sqlDatabaseDTO.getDbUserName());
                    sqlSaveDTO.setDataUrl(sqlDatabaseDTO.getDbDatabase());
                    sqlSaveDTO.setDataName(sqlDatabaseDTO.getDbName());
                    sqlSaveDTO.setSql(sqlDatabaseDTO.getSql());
                    // 将List<List<Object>>转换为JSON字符串存储到resultJSON字段
                    sqlSaveDTO.setResultColumns(new ObjectMapper().writeValueAsString(columns));
                    sqlSaveDTO.setResultRow(new ObjectMapper().writeValueAsString(rowsData));
                    log.info("插入数据库："+sqlSaveDTO.getResultRow());
                    sqlSave.insertSql(sqlSaveDTO);
                    log.info("插入数据库成功："+sqlSaveDTO.getSql());
                    connection.close();
                } catch (SQLException e) {
                    log.error("sql语句有问题", e.getMessage());
                    result.setCode(99);
                    result.setMessage("SQL执行失败: " + e.getMessage());
                    return result;
                } catch (JsonProcessingException e) {
                    throw new RuntimeException(e);
                }
            } catch (SQLException e) {
                log.error("SQL语句有问题:", e.getMessage());
                result.setCode(99);
                result.setMessage("SQL语句有问题: " + e.getMessage());
                return result;
            }
        } catch (SQLException e) {
            log.error("SQL没有正确链接数据库", e.getMessage());
            result.setCode(2);
            result.setMessage("SQL执行失败: " + e.getMessage());
        }

        return result;
    }


//    public void close() {
//        try {
//            Connection connection = dataSource.getConnection();
//            connection.close();
//            log.info("数据库连接关闭成功");
//        } catch (SQLException e) {
//            throw new RuntimeException(e);
//        }
//
//    }


}

